﻿Imports System.Data
Imports System.Data.SqlClient

Public Class GenCode

#Region "Var"
    Private AutoNumber As Boolean
    Private mData As DataTable
    Private tblName As String
    Private ColName() As String
    Private ColType() As String
    Private PrimaryKey() As Boolean
    Private PrimaryKeyCount As Integer = 0
    Private Leng As Integer
    Private _namesp As String = ""
    Private _connString As String = ""
#End Region

#Region "Constructor"
    Private Function IsPrimary(ByVal col As String, ByVal pk As DataTable) As Boolean
        For i As Integer = 0 To pk.Rows.Count - 1
            If col = pk.Rows(i)(0) Then
                Return True
            End If
        Next
        Return False
    End Function

    Private Function LoadAllField(ByVal tablename As String) As DataTable
        Dim strSQL As String = "SELECT A.name, B.name As typeName, A.length, B.length As sizeLength, A.Autoval " _
                        + " FROM SysColumns A, SysTypes B, SysObjects C " _
                        + " WHERE A.id=C.id AND A.xtype=B.xtype AND B.name<>'sysname' " _
                        + " AND C.name='" + tablename + "' ORDER BY A.ColOrder"

        Dim da As New SqlDataAdapter(strSQL, _connString)
        Dim dt As New DataTable
        da.Fill(dt)
        Return dt
    End Function

    Private Function LoadPrimaryKey(ByVal tablename As String) As DataTable
        Dim strSQL As String = "select c.name " _
                        + " from sysindexes i " _
                        + " join sysobjects o ON i.id = o.id " _
                        + " join sysobjects pk ON i.name = pk.name " _
                        + " AND pk.parent_obj = i.id " _
                        + " AND pk.xtype = 'PK' " _
                        + " join sysindexkeys ik on i.id = ik.id " _
                        + " and i.indid = ik.indid  " _
                        + " join syscolumns c ON ik.id = c.id  " _
                        + " AND ik.colid = c.colid  " _
                        + " where o.name = '" + tablename + "'  " _
                        + " order by ik.keyno"

        Dim da As New SqlDataAdapter(strSQL, _connString)
        Dim dt As New DataTable
        da.Fill(dt)
        Return dt
    End Function


    Public Sub New(ByVal tablename As String, ByVal _namespace As String, ByVal connstring As String)
        _namesp = _namespace
        _connString = connstring
        tblName = tablename
        mData = LoadAllField(tablename)
        Dim pk As DataTable = LoadPrimaryKey(tablename)
        Leng = mData.Rows.Count

        ReDim ColName(mData.Rows.Count - 1)
        ReDim ColType(mData.Rows.Count - 1)
        ReDim PrimaryKey(mData.Rows.Count - 1)

        Dim i As Integer
        For i = 0 To Leng - 1
            ColName(i) = mData.Rows(i)("name")
            ColType(i) = mData.Rows(i)("typeName")
            If ColType(i) = "bigint" Then ColType(i) = "long"
            If ColType(i) = "smallint" Then ColType(i) = "byte"
            If ColType(i) = "datetime" Then ColType(i) = "DateTime"
            If ColType(i) = "float" Then ColType(i) = "double"
            If ColType(i) = "bit" Then ColType(i) = "bool"
            If (ColType(i) = "char" Or ColType(i) = "nchar" Or _
                ColType(i) = "varchar" Or ColType(i) = "nvarchar" _
                Or ColType(i) = "ntext" Or ColType(i) = "ntext") Then ColType(i) = "string"
        Next

        If pk.Rows.Count = 0 Then
            PrimaryKey(0) = True
            PrimaryKeyCount = 1
        Else
            For i = 0 To Leng - 1
                PrimaryKey(i) = IsPrimary(ColName(i), pk)
                If PrimaryKey(i) Then PrimaryKeyCount += 1
            Next
        End If

        AutoNumber = False
        For i = 0 To Leng - 1
            If Not mData.Rows(i)("Autoval") Is DBNull.Value Then AutoNumber = True
        Next
    End Sub
#End Region

#Region "sub Function"
    Private Function Header() As String
        Dim code As String = ""
        code += "//---------------------------------------------------------------------------" + vbCrLf
        code += "// Author : AWAS JSC" + vbCrLf
        code += "// Created Date : " + Now.ToLongDateString() + vbCrLf
        code += "//---------------------------------------------------------------------------" + vbCrLf + vbCrLf + vbCrLf
        Return code
    End Function
   
    Private Function GetInitValue(ByVal coltype As String) As String
        Select Case coltype.ToLower()
            Case "long"
                Return "0"
            Case "int"
                Return "0"
            Case "byte"
                Return "0"
            Case "double"
                Return "0"
            Case "float"
                Return "0"
            Case "object"
                Return "null"
            Case "string"
                Return """""" ' hai dau nhay ""
            Case "bool"
                Return "false"
            Case "datetime"
                Return "DateTime.Now"
        End Select
        Return """"""
    End Function
    Private Function new1Info() As String
        Dim code As String = ""
        code += vbTab + vbTab + "public " + tblName + "Info()" + vbCrLf
        code += vbTab + vbTab + "{" + vbCrLf + vbCrLf
        code += vbTab + vbTab + "}" + vbCrLf + vbCrLf
        Return code
    End Function
    Private Function new1_DAL() As String
        Dim code As String = ""
        code += vbTab + vbTab + "public " + tblName + "Controller(){}" + vbCrLf
        Return code
    End Function
    Private Function Insert() As String
        Dim code As String = ""
        Dim i As Integer

        code += vbTab + vbTab + "private const string INSERT_" + tblName.ToUpper() + " = ""INSERT INTO " + tblName + " ("
        For i = 0 To Leng - 2
            If mData.Rows(i)("Autoval") Is DBNull.Value Then
                code += ColName(i) + ", "
            End If
        Next
        code += ColName(Leng - 1) + ") """ + vbCrLf + vbTab + vbTab + " + "" VALUES ("
        For i = 0 To Leng - 2
            If mData.Rows(i)("Autoval") Is DBNull.Value Then
                code += "@" + ColName(i) + ", "
            End If
        Next
        code += "@" + ColName(Leng - 1) + ")"";" + vbCrLf
        code += vbTab + vbTab + "public int Insert(" + tblName + "Info my" + tblName + ")" + vbCrLf
        code += vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + "int result = 0;" + vbCrLf
        code += vbTab + vbTab + vbTab + "try" + vbCrLf
        code += vbTab + vbTab + vbTab + "{" + vbCrLf

        If AutoNumber Then
            code += vbTab + vbTab + vbTab + vbTab + "ArrayList param = new ArrayList();" + vbCrLf
            For i = 0 To Leng - 1
                If mData.Rows(i)("Autoval") Is DBNull.Value Then
                    code += vbTab + vbTab + vbTab + vbTab + "param.Add(new DataParameter(""@" + ColName(i) + """, my" + tblName + "." + ColName(i) + "));" + vbCrLf
                End If
            Next
        Else
            code += vbTab + vbTab + vbTab + vbTab + "ArrayList param = new ArrayList();" + vbCrLf
            For i = 0 To Leng - 1
                code += vbTab + vbTab + vbTab + vbTab + "param.Add(new DataParameter(""@" + ColName(i) + """, my" + tblName + "." + ColName(i) + "));" + vbCrLf
            Next
        End If

        code += vbTab + vbTab + vbTab + vbTab + "result = DataFactory.Execute(INSERT_" + tblName.ToUpper() + ", param);" + vbCrLf
        code += vbTab + vbTab + vbTab + "}" + vbCrLf
        code += vbTab + vbTab + vbTab + "catch(Exception ex)" + vbCrLf
        code += vbTab + vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + vbTab + "throw ex;" + vbCrLf
        code += vbTab + vbTab + vbTab + "}" + vbCrLf
        code += vbTab + vbTab + vbTab + "return result;" + vbCrLf
        code += vbTab + vbTab + "}" + vbCrLf + vbCrLf

        Return code
    End Function
    Private Function Update() As String
        Dim code As String = ""
        Dim i As Integer

        code += vbTab + vbTab + "private const string UPDATE_" + tblName.ToUpper() + " = ""UPDATE " + tblName + " SET """ + vbCrLf
        For i = 0 To Leng - 1
            If Not PrimaryKey(i) Then
                code += vbTab + vbTab + vbTab + " + "" " + ColName(i) + " = @" + ColName(i) + ", """ + vbCrLf
            End If
        Next
        code = code.Substring(0, code.Length - 5) + " """ + vbCrLf
        code += vbTab + vbTab + vbTab + " + "" WHERE ("
        For i = 0 To Leng - 1
            If PrimaryKey(i) Then
                code += ColName(i) + " = @" + ColName(i) + ") AND ("
            End If
        Next
        code = code.Substring(0, code.Length - 6) + """;" + vbCrLf

        code += vbTab + vbTab + "public int Update(" + tblName + "Info my" + tblName + ")" + vbCrLf
        code += vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + "int result = 0;" + vbCrLf
        code += vbTab + vbTab + vbTab + "try" + vbCrLf
        code += vbTab + vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + vbTab + "ArrayList param = new ArrayList();" + vbCrLf
        ' rac roi qua thang access lam an nhu cut
        For i = 0 To Leng - 1
            If Not PrimaryKey(i) Then
                code += vbTab + vbTab + vbTab + vbTab + "param.Add(new DataParameter(""@" + ColName(i) + """, my" + tblName + "." + ColName(i) + "));" + vbCrLf
            End If
        Next
        For i = 0 To Leng - 1
            If PrimaryKey(i) Then
                code += vbTab + vbTab + vbTab + vbTab + "param.Add(new DataParameter(""@" + ColName(i) + """, my" + tblName + "." + ColName(i) + "));" + vbCrLf
            End If
        Next
        code += vbTab + vbTab + vbTab + vbTab + "result = DataFactory.Execute(UPDATE_" + tblName.ToUpper() + ", param);" + vbCrLf
        code += vbTab + vbTab + vbTab + "}" + vbCrLf
        code += vbTab + vbTab + vbTab + "catch(Exception ex)" + vbCrLf
        code += vbTab + vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + vbTab + "throw ex;" + vbCrLf
        code += vbTab + vbTab + vbTab + "}" + vbCrLf
        code += vbTab + vbTab + vbTab + "return result;" + vbCrLf
        code += vbTab + vbTab + "}" + vbCrLf + vbCrLf

        Return code
    End Function

    Private Function GetIndexPrimary(ByVal count As Integer) As Integer
        For i As Integer = 0 To Leng - 1
            If PrimaryKey(i) Then
                count -= 1
                If count = -1 Then Return i
            End If
        Next
        Return -1
    End Function
    Private Function Del() As String
        Dim code As String = ""
        Dim i As Integer

        code += vbTab + vbTab + "private const string DELETE_" + tblName.ToUpper() + " = ""DELETE FROM " + tblName + " WHERE ("
        For i = 0 To Leng - 1
            If PrimaryKey(i) Then
                code += ColName(i) + " = @" + ColName(i) + ") AND ("
            End If
        Next
        code = code.Substring(0, code.Length - 6) + """;" + vbCrLf

        code += vbTab + vbTab + "public int Delete("
        For i = 0 To Leng - 1
            If PrimaryKey(i) Then
                code += ColType(i) + " " + ColName(i) + ", "
            End If
        Next
        code = code.Substring(0, code.Length - 2) + ")" + vbCrLf
        code += vbTab + vbTab + "{" + vbCrLf

        code += vbTab + vbTab + vbTab + "int result = 0;" + vbCrLf
        code += vbTab + vbTab + vbTab + "try" + vbCrLf
        code += vbTab + vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + vbTab + "ArrayList param = new ArrayList();" + vbCrLf
        For i = 0 To PrimaryKeyCount - 1
            Dim idx As Integer = GetIndexPrimary(i)
            If idx <> -1 Then
                code += vbTab + vbTab + vbTab + vbTab + "param.Add(new DataParameter(""@" + ColName(idx) + """, " + ColName(idx) + "));" + vbCrLf
            End If
        Next
        code += vbTab + vbTab + vbTab + vbTab + "result = DataFactory.Execute(DELETE_" + tblName.ToUpper() + ", param);" + vbCrLf
        code += vbTab + vbTab + vbTab + "}" + vbCrLf
        code += vbTab + vbTab + vbTab + "catch(Exception ex)" + vbCrLf
        code += vbTab + vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + vbTab + "throw ex;" + vbCrLf
        code += vbTab + vbTab + vbTab + "}" + vbCrLf
        code += vbTab + vbTab + vbTab + "return result;" + vbCrLf
        code += vbTab + vbTab + "}" + vbCrLf + vbCrLf

        Return code
    End Function
    Private Function Convert() As String
        Dim code As String = ""
        Dim i As Integer

        code += vbTab + vbTab + "public " + tblName + "Info Converting(DataRow dr" + tblName + ")" + vbCrLf
        code += vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + "" + tblName + "Info result = null;" + vbCrLf
        code += vbTab + vbTab + vbTab + "try" + vbCrLf
        code += vbTab + vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + vbTab + "result = new " + tblName + "Info();" + vbCrLf
        For i = 0 To Leng - 1
            code += vbTab + vbTab + vbTab + vbTab + "if(dr" + tblName + "[""" + ColName(i) + """] != DBNull.Value)" + vbCrLf
            code += vbTab + vbTab + vbTab + vbTab + vbTab
            Select Case ColType(i)
                Case "bool"
                    code += "result." + ColName(i) + " = Convert.ToBoolean(dr" + tblName + "[""" + ColName(i) + """]);" + vbCrLf
                Case "int"
                    code += "result." + ColName(i) + " = Convert.ToInt32(dr" + tblName + "[""" + ColName(i) + """]);" + vbCrLf
                Case "long"
                    code += "result." + ColName(i) + " = Convert.ToInt64(dr" + tblName + "[""" + ColName(i) + """]);" + vbCrLf
                Case "short"
                    code += "result." + ColName(i) + " = Convert.ToInt16(dr" + tblName + "[""" + ColName(i) + """]);" + vbCrLf
                Case "decimal"
                    code += "result." + ColName(i) + " = Convert.ToDecimal(dr" + tblName + "[""" + ColName(i) + """]);" + vbCrLf
                Case "double"
                    code += "result." + ColName(i) + " = Convert.ToDouble(dr" + tblName + "[""" + ColName(i) + """]);" + vbCrLf
                Case "float"
                    code += "result." + ColName(i) + " = Convert.ToDouble(dr" + tblName + "[""" + ColName(i) + """]);" + vbCrLf
                Case "DateTime"
                    code += "result." + ColName(i) + " = Convert.ToDateTime(dr" + tblName + "[""" + ColName(i) + """]);" + vbCrLf
                Case Else
                    code += "result." + ColName(i) + " = Convert.ToString(dr" + tblName + "[""" + ColName(i) + """]);" + vbCrLf
            End Select
        Next
        code += vbTab + vbTab + vbTab + "}" + vbCrLf
        code += vbTab + vbTab + vbTab + "catch(Exception ex)" + vbCrLf
        code += vbTab + vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + vbTab + "throw ex;" + vbCrLf
        code += vbTab + vbTab + vbTab + "}" + vbCrLf
        code += vbTab + vbTab + vbTab + "return result;" + vbCrLf
        code += vbTab + vbTab + "}" + vbCrLf + vbCrLf

        Return code
    End Function
    Private Function Load() As String
        Dim code As String = ""
        Dim i As Integer

        code += vbTab + vbTab + "private const string LOAD_" + tblName.ToUpper() + " = ""SELECT * FROM " + tblName + " WHERE ("
        For i = 0 To Leng - 1
            If PrimaryKey(i) Then
                code += ColName(i) + " = @" + ColName(i) + ") AND ("
            End If
        Next
        code = code.Substring(0, code.Length - 6) + """;" + vbCrLf

        code += vbTab + vbTab + "public " + tblName + "Info Load("
        For i = 0 To Leng - 1
            If PrimaryKey(i) Then
                code += ColType(i) + " " + ColName(i) + ", "
            End If
        Next
        code = code.Substring(0, code.Length - 2) + ")" + vbCrLf
        code += vbTab + vbTab + "{" + vbCrLf

        code += vbTab + vbTab + vbTab + "" + tblName + "Info result = null;" + vbCrLf
        code += vbTab + vbTab + vbTab + "try" + vbCrLf
        code += vbTab + vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + vbTab + "ArrayList param = new ArrayList();" + vbCrLf
        For i = 0 To PrimaryKeyCount - 1
            Dim idx As Integer = GetIndexPrimary(i)
            If idx <> -1 Then
                code += vbTab + vbTab + vbTab + vbTab + "param.Add(new DataParameter(""@" + ColName(idx) + """, " + ColName(idx) + "));" + vbCrLf
            End If
        Next
        code += vbTab + vbTab + vbTab + vbTab + "DataTable dt = DataFactory.SelectTable(LOAD_" + tblName.ToUpper() + ", param);" + vbCrLf
        code += vbTab + vbTab + vbTab + vbTab + "if(dt.Rows.Count > 0) result = Converting(dt.Rows[0]);" + vbCrLf
        code += vbTab + vbTab + vbTab + "}" + vbCrLf
        code += vbTab + vbTab + vbTab + "catch(Exception ex)" + vbCrLf
        code += vbTab + vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + vbTab + "throw ex;" + vbCrLf
        code += vbTab + vbTab + vbTab + "}" + vbCrLf
        code += vbTab + vbTab + vbTab + "return result;" + vbCrLf
        code += vbTab + vbTab + "}" + vbCrLf + vbCrLf

        Return code
    End Function
    Private Function LoadTable() As String
        Dim code As String = ""

        code += vbTab + vbTab + "private const string LOAD_LIST_" + tblName.ToUpper() + " = ""SELECT * FROM " + tblName + """;" + vbCrLf
        code += vbTab + vbTab + "public DataTable LoadTable()" + vbCrLf
        code += vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + "DataTable result = null;" + vbCrLf
        code += vbTab + vbTab + vbTab + "try" + vbCrLf
        code += vbTab + vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + vbTab + "result = DataFactory.SelectTable(LOAD_LIST_" + tblName.ToUpper() + ");" + vbCrLf
        code += vbTab + vbTab + vbTab + "}" + vbCrLf
        code += vbTab + vbTab + vbTab + "catch(Exception ex)" + vbCrLf
        code += vbTab + vbTab + vbTab + "{" + vbCrLf
        code += vbTab + vbTab + vbTab + vbTab + "throw ex;" + vbCrLf
        code += vbTab + vbTab + vbTab + "}" + vbCrLf
        code += vbTab + vbTab + vbTab + "return result;" + vbCrLf
        code += vbTab + vbTab + "}" + vbCrLf + vbCrLf

        Return code
    End Function
#End Region

#Region "main Function"
    Public Function GetInfo() As String
        Dim code As String = Header()
        Dim i As Integer
        ' Khai báo lớp
        code += "using System;" + vbCrLf + vbCrLf
        code += "namespace " + _namesp + vbCrLf + "{" + vbCrLf
        code += vbTab + "public class " + tblName + "Info" + vbCrLf
        code += vbTab + "{" + vbCrLf

        ' Hàm khởi tạo không tham số
        code += vbTab + vbTab + "#region Constructor" + vbCrLf
        code += new1Info()
        code += vbTab + vbTab + "#endregion" + vbCrLf + vbCrLf

        ' Các biến số
        code += vbTab + vbTab + "#region Vars" + vbCrLf
        For i = 0 To Leng - 1
            code += vbTab + vbTab + "private " + ColType(i) + " _" + ColName(i) + " = " + GetInitValue(ColType(i)) + ";" + vbCrLf
        Next
        code += vbTab + vbTab + "#endregion" + vbCrLf + vbCrLf


        code += vbTab + vbTab + "#region Property" + vbCrLf
        ' Các thuộc tính
        For i = 0 To mData.Rows.Count - 1
            code += vbTab + vbTab + "public " + ColType(i) + " " + ColName(i) + vbCrLf
            code += vbTab + vbTab + "{" + vbCrLf
            code += vbTab + vbTab + vbTab + "get{ return _" + ColName(i) + "; }" + vbCrLf

            code += vbTab + vbTab + vbTab + "set{ _" + ColName(i) + " = value; }" + vbCrLf
            code += vbTab + vbTab + "}" + vbCrLf
        Next
        code += vbTab + vbTab + "#endregion" + vbCrLf + vbCrLf

        ' Đóng Class
        code += vbTab + "}" + vbCrLf
        code += "}" + vbCrLf
        Return code
    End Function
    Public Function GetDAL() As String
        Dim code As String = Header()
        ' Khai báo lớp
        code += "using System;" + vbCrLf
        code += "using System.Collections;" + vbCrLf
        code += "using System.Data;" + vbCrLf
        code += "using System.Data.SqlClient;" + vbCrLf
        code += "using vs.Core;" + vbCrLf + vbCrLf
        code += "namespace " + _namesp + vbCrLf + "{" + vbCrLf
        code += vbTab + "public class " + tblName + "Controller" + vbCrLf
        code += vbTab + "{" + vbCrLf

        ' Hàm khởi tạo không tham số
        code += vbTab + vbTab + "#region Constructor" + vbCrLf
        code += new1_DAL()
        code += vbTab + vbTab + "#endregion" + vbCrLf + vbCrLf

        code += vbTab + vbTab + "#region Function" + vbCrLf
        ' Insert
        code += Insert()
        ' Update
        code += Update()
        ' Del
        code += Del()
        ' Get Info
        code += Convert()
        ' Load
        code += Load()
        ' Load List 4 Table
        code += LoadTable()
        ' Check Exist
        code += vbTab + vbTab + "#endregion" + vbCrLf + vbCrLf
        ' Đóng Class
        code += vbTab + "}" + vbCrLf
        code += "}" + vbCrLf
        Return code
    End Function
#End Region

#Region "Bind"
    Public Function BindControl() As String
        Dim code As String = ""
        Dim i As Integer

        ' Các biến số
        For i = 0 To Leng - 1
            code += "txt" + ColName(i) + ".Text = v." + ColName(i) + ";" + vbCrLf
        Next
        Return code
    End Function
#End Region

#Region "Gen Control"
    Public Function GenControl() As String
        Dim code As String = "<table width=""100%"">" + vbCrLf
        Dim i As Integer

        code += vbTab + "<tr>" + vbCrLf
        code += vbTab + vbTab + "<td></td>" + vbCrLf
        code += vbTab + vbTab + "<td><asp:Label ID=""lblMessage"" runat=""server"" Visible=""false"" Font-Bold=""true"" ForeColor=""red""></asp:Label></td>" + vbCrLf
        code += vbTab + "</tr>" + vbCrLf

        ' Các biến số
        For i = 0 To Leng - 1
            code += vbTab + "<tr>" + vbCrLf
            code += vbTab + vbTab + "<td>" + ColName(i) + "</td>" + vbCrLf
            code += vbTab + vbTab + "<td><asp:TextBox ID=""txt" + ColName(i) + """ runat=""server"" style=""width:300px;""></asp:TextBox></td>" + vbCrLf
            code += vbTab + "</tr>" + vbCrLf
        Next
        code += vbTab + "<tr>" + vbCrLf
        code += vbTab + vbTab + "<td></td>" + vbCrLf
        code += vbTab + vbTab + "<td><asp:Button ID=""btnSave"" runat=""server"" Text=""Save""></asp:Button></td>" + vbCrLf
        code += vbTab + "</tr>" + vbCrLf
        code += "</table>"
        Return code
    End Function
#End Region


#Region "Gencode script oracle"
    Private Function IdentityColumn(ByVal ora() As String) As String
        For i As Integer = 0 To Leng - 1
            If Not mData.Rows(i)("Autoval") Is DBNull.Value Then
                If ora(i) = "NUMBER" Or ora(i) = "INTEGER" Then
                    Return ColName(i)
                End If
            End If
        Next
        Return ""
    End Function

    Private Function OracleDataType() As String()

        Dim ora() As String
        ReDim ora(mData.Rows.Count - 1)

        For i As Integer = 0 To Leng - 1
            ora(i) = "VARCHAR2(250)"
            If ColType(i) = "long" Or ColType(i) = "int" Or ColType(i) = "byte" Or ColType(i) = "bool" Then ora(i) = "INTEGER"
            If ColType(i) = "double" Or ColType(i) = "float" Then ora(i) = "NUMBER"
            If ColType(i) = "datetime" Then ora(i) = "DATE"
        Next

        Return ora
    End Function

    Public Function GenOracleScript() As String
        Dim ora() As String = OracleDataType()
        Dim code As String = "--" + tblName.ToUpper + vbCrLf
        code += "CREATE TABLE @@TABLENAME (" + vbCrLf
        For i As Integer = 0 To Leng - 2
            Dim col As String = ColName(i).ToUpper + " " + ora(i).ToUpper

            If (PrimaryKey(i)) Then
                col += " PRIMARY KEY "
            End If

            code += col + "," + vbCrLf
        Next
        code += ColName(Leng - 1).ToUpper + " " + ora(Leng - 1).ToUpper + vbCrLf + ");" + vbCrLf + vbCrLf

        Dim identity As String = IdentityColumn(ora)

        If (Not identity.Equals("")) Then
            code += "CREATE SEQUENCE SEQ_@@TABLENAME" + vbCrLf
            code += "MINVALUE 1" + vbCrLf
            code += "MAXVALUE 999999999999999999" + vbCrLf
            code += "START WITH 1" + vbCrLf
            code += "INCREMENT BY 1" + vbCrLf
            code += "NOCACHE " + vbCrLf
            code += ";" + vbCrLf + vbCrLf


            code += "CREATE OR REPLACE TRIGGER AUTOVAL_@@TABLENAME" + vbCrLf
            code += "BEFORE INSERT ON @@TABLENAME" + vbCrLf
            code += "FOR EACH ROW" + vbCrLf
            code += "BEGIN" + vbCrLf
            code += "IF :new.ID IS NULL THEN" + vbCrLf
            code += vbTab + "Select Case SEQ_SYS_ACTION.nextval" + vbCrLf
            code += vbTab + "INTO :new.@@ID FROM DUAL;" + vbCrLf
            code += "END IF;" + vbCrLf
            code += "END;" + vbCrLf + vbCrLf

            code = code.Replace("@@ID", identity.ToUpper)
        End If
        code = code.Replace("@@TABLENAME", tblName.ToUpper)
       
        Return code

    End Function
#End Region

#Region "add node"
    Public Function GenAddNodeXML() As String
        Dim code As String = "XmlElement e = doc.CreateElement(""elem"");" + vbCrLf

        For i As Integer = 0 To ColName.Length - 1
            code += "CommonFunction.AddNode(doc, e, """ + ColName(i) + """, dt.Rows[i][""" + ColName(i) + """].ToString());" + vbCrLf
        Next

        Return code
    End Function
#End Region

End Class
